﻿using DatabaseSchemaReader;
using DatabaseSchemaReader.Data;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlGen
{
    public partial class Form1 : Form
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
        const string providername = "System.Data.SqlClient";
        private bool IncludeIdentity;

        public Form1()
        {
            InitializeComponent();
        }

        private string GetInsertSql(string tableName, string providername, string connectionString, DatabaseSchemaReader.DataSchema.DatabaseSchema schema)
        {
            //just get the "Categories" table
            var table = schema.FindTableByName(tableName);
            //use the reader helper
            var rdr = new DatabaseSchemaReader.Data.Reader(table, connectionString, providername);
            rdr.PageSize = 10000;
            DataTable dt = rdr.Read();

            var insertWriter = new InsertWriter(table, dt);
            //if there are identity columns, write them too? Default is false
            insertWriter.IncludeIdentity = IncludeIdentity;
            insertWriter.IncludeBlobs = true;
            //specify the dialect
            string txt = insertWriter.Write(DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
            return txt;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ListTables();
        }

        private void ListTables()
        {
            var dbReader = new DatabaseReader(connectionString, providername);
            var schema = dbReader.AllTables();
            this.ed_tables.DataSource = schema;
        }

        private void btnReflesh_Click(object sender, EventArgs e)
        {
            ListTables();
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            const string providername = "System.Data.SqlClient";
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
            var tableName = ed_tables.SelectedValue.ToString();
            IncludeIdentity = ed_IncludeIdentity.Checked;
            var dbReader = new DatabaseReader(connectionString, providername);
            var schema = dbReader.ReadAll();
            string txt = GetInsertSql(tableName, providername, connectionString, schema);
            ed_return.Text = txt;
        }

        private void btnSQL_Click(object sender, EventArgs e)
        {
            var tableName = this.ed_tables.SelectedValue.ToString();
            var sql = @"
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.XuenianXueqi ADD
    IsDeleted bit NOT NULL CONSTRAINT DF_XuenianXueqi_IsDelete DEFAULT 0,
	CreatedTime datetime NOT NULL CONSTRAINT DF_XuenianXueqi_CreatedTime DEFAULT GetDate()
GO
ALTER TABLE dbo.XuenianXueqi SET(LOCK_ESCALATION = TABLE)
GO
COMMIT
";
            sql = sql.Replace("XuenianXueqi", tableName);
            ed_return.Text = sql;
        }
    }
}
